package database;

import gui.Desktop;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.Calendar;
import java.util.Locale;
import java.util.ResourceBundle;
import java.util.prefs.Preferences;

import org.apache.log4j.Logger;

/**
 * Trida pro praci s databazi
 * 
 * @author dove
 * 
 */
public class MySQLConnection {

	private static Connection con = null;

	private static int actualId_game;
	private static int actualId_session;
	private static String actualLogin;
	private static String myLogin;

	public static int moveNumber = 0;

	private static int won = 0;
	private static int lost = 0;
	private static int drawn = 0;

	private static Logger logger = Logger.getLogger("Log");
	private static String[][] data;

	private static Preferences prefs;

	private static Time nullTime;

	private static long timeStart;

	private static String language;

	private static ResourceBundle messages;

	/**
	 * Konstruktor pripojujici se k databazi
	 */
	public static void connect() {
		logger.info("pokus");

		prefs = Preferences.userRoot();
		language = prefs.get("Language", "en");
		Locale currentLocale = new Locale(language);
		messages = ResourceBundle.getBundle("Lang", currentLocale);

		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			try {
				String driverName = "org.gjt.mm.mysql.Driver";
				Class.forName(driverName);
				String serverName = "localhost";
				String mydatabase = "chess";
				String url = "jdbc:mysql://" + serverName + "/" + mydatabase;
				String username = prefs.get("databaseLogin", "root");
				logger.debug(username);
				String password = prefs.get("databasePassword", "");
				con = DriverManager.getConnection(url, username, password);

				MySQLConnection.insertSession(won, lost);
			} catch (ClassNotFoundException e) {
				logger.error("", e);
			} catch (SQLException e) {
				logger.error("", e);
				prefs.put("checkDatabase", "false");
				Desktop
						.infoMessage(messages
								.getString("database.cant_connect"));
			}

		}
	}

	/**
	 * Metoda pridava informace o sezeni
	 * 
	 * @param game_won
	 *            pocet vyhranych partii
	 * @param game_lost
	 *            pocet prohranych partii
	 * @param start_time
	 *            cas kdy sezeni zacalo
	 */
	public static void insertSession(int game_won, int game_lost) {
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			timeStart = Calendar.getInstance().getTimeInMillis();
			nullTime = new java.sql.Time(-3600000);

			long dat = Calendar.getInstance().getTimeInMillis();
			java.sql.Date t = new java.sql.Date(dat);

			try {
				PreparedStatement a = con
						.prepareStatement("INSERT INTO Session (game_won, game_lost, date_game, spend_time) VALUES (?,?,?,?);");
				a.setInt(1, game_won);
				a.setInt(2, game_lost);
				a.setDate(3, t);
				a.setTime(4, nullTime);
				logger.debug(a);
				a.executeUpdate();
				a.close();

				PreparedStatement b = con
						.prepareStatement("select LAST_INSERT_ID()");
				logger.debug(b);
				ResultSet rs = b.executeQuery();
				while (rs.next()) {
					logger.debug("ID_SESSION:" + rs.getInt(1));
					actualId_session = rs.getInt(1);
				}
				b.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
	}

	/**
	 * Metoda pridava informace o relaci
	 * 
	 * @param game_won
	 *            pocet vyhranych partii
	 * @param game_lost
	 *            pocet prohranych partii
	 * @param start_time
	 *            cas kdy relace zacala
	 */
	public static void updateSpendTime() {
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))
				&& actualId_session != 0) {
			long timeEnd = Calendar.getInstance().getTimeInMillis();
			Time spendTime = new Time(timeEnd - timeStart - 3600000);

			try {
				PreparedStatement a = con
						.prepareStatement("UPDATE Session SET spend_time = ? WHERE id_session = ?");
				a.setTime(1, spendTime);
				a.setInt(2, actualId_session);
				logger.debug(a);
				a.executeUpdate();
				a.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
	}

	/**
	 * Vlozi informace o protihraci
	 * 
	 * @param name
	 *            login protihrace
	 * @param rating
	 *            jeho aktualni rating
	 */
	public static void insertPlayer(String name, int rating) {
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			try {
				PreparedStatement a = con
						.prepareStatement("insert into Player (Login,last_rating)VALUES (?,?)");
				a.setString(1, name);
				a.setInt(2, rating);
				logger.debug("MYSQL:" + a);
				a.executeUpdate();
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
	}

	/**
	 * Kontroluje jestli hrac uz v databazi existuje
	 * 
	 * @param login
	 *            hrace
	 * @return je hrac uz zapsan v databazi
	 */
	public static boolean isPlayer(String login) {
		boolean result = false;
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			try {
				PreparedStatement a = con
						.prepareStatement("SELECT Login FROM Player WHERE login = ?");
				a.setString(1, login);
				logger.debug(a);

				ResultSet rs = a.executeQuery();
				result = rs.next();
				a.close();
			} catch (SQLException e) {
				logger.error("", e);
				e.printStackTrace();
			}
		}
		return result;
	}

	/**
	 * Zjistuje pocet vyher aktualniho hrace
	 * 
	 * @return pocet vyher hrace
	 */
	public static int getPlayerWon(String login) {
		int result = 0;
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			try {
				PreparedStatement query = con
						.prepareStatement("SELECT won FROM Player WHERE login = ?");
				query.setString(1, login);
				logger.debug(query);

				ResultSet rs = query.executeQuery();
				rs.next();
				result = rs.getInt("won");
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
		return result;
	}

	/**
	 * Zjistuje pocet proher aktualniho hrace
	 * 
	 * @return pocet proher hrace
	 */
	public static int getPlayerLost(String login) {
		int result = 0;
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			try {
				PreparedStatement query = con
						.prepareStatement("SELECT lost FROM Player WHERE login = ?");
				query.setString(1, login);
				logger.debug(query);

				ResultSet rs = query.executeQuery();
				rs.next();
				result = rs.getInt("lost");
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
		return result;
	}

	/**
	 * Zjistuje pocet remiz aktualniho hrace
	 * 
	 * @return pocet remiz hrace
	 */
	public static int getPlayerDrawn(String login) {
		int result = 0;
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			try {
				PreparedStatement query = con
						.prepareStatement("SELECT drawn FROM Player WHERE login = ?");
				query.setString(1, login);
				logger.debug(query);

				ResultSet rs = query.executeQuery();
				rs.next();
				result = rs.getInt("drawn");
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
		return result;
	}

	/**
	 * Metoda pro zjisteni loginu vsech protihracu v databazi
	 * 
	 * @return pole s loginy hracu
	 */
	public static String[] getPlayersNames() {
		ResultSet rs;
		int count = 0;
		String[] result = new String[count];
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			try {
				PreparedStatement query = con
						.prepareStatement("SELECT COUNT(*)'login' FROM Player");
				rs = query.executeQuery();
				rs.next();
				count = rs.getInt("login");
				logger.debug("MYSQL:" + count);
				result = new String[count];
				PreparedStatement a = con
						.prepareStatement("SELECT login FROM Player");
				logger.debug(a);
				rs = a.executeQuery();
				for (int x = 0; x < count; x++) {
					rs.next();
					result[x] = rs.getString("login");
				}
				query.close();
				a.close();
			} catch (SQLException e) {
				logger.error("", e);
				e.printStackTrace();
			}
		}
		return result;
	}

	/**
	 * Metoda vraci detailni vypis vsech odehranych her
	 * 
	 * @return pole s daty o vsech hrach
	 */
	public static Object[][] getGamesInfo() {
		ResultSet rs;
		int count = 0;
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			try {
				PreparedStatement query = con
						.prepareStatement("SELECT COUNT(*)'id_game' FROM Games");

				rs = query.executeQuery();
				rs.next();
				count = rs.getInt("id_game");
				logger.debug("MYSQL:" + count);
				data = new String[count][5];
				PreparedStatement query2 = con
						.prepareStatement("SELECT login,style,game_date,time_game,result FROM Games");
				rs = query2.executeQuery();
				for (int x = 0; x < count; x++) {
					rs.next();
					data[x][0] = rs.getString("login");
					data[x][1] = rs.getString("style");
					data[x][2] = rs.getString("game_date");
					data[x][3] = rs.getString("time_game");
					data[x][4] = rs.getString("result");
				}
				query.close();
				query2.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
		return data;
	}

	/**
	 * Metoda vraci detailni vypis vsech odehranych turnaju
	 * 
	 * @return pole s daty o vsech turnajich
	 */
	public static Object[][] getTournamentsInfo() {
		ResultSet rs;
		int count = 0;
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			try {
				PreparedStatement query = con
						.prepareStatement("SELECT COUNT(*)'id_tournament' FROM Tournament");

				rs = query.executeQuery();
				rs.next();
				count = rs.getInt("id_tournament");
				logger.debug("MYSQL:" + count);
				data = new String[count][6];

				PreparedStatement query2 = con
						.prepareStatement("SELECT game_type , time_limit , number_of_players , final_place , starting_time , starting_date FROM Tournament ");
				rs = query2.executeQuery();
				for (int x = 0; x < count; x++) {
					rs.next();
					data[x][0] = rs.getString("game_type");
					data[x][1] = rs.getString("time_limit");
					data[x][2] = rs.getString("number_of_players");
					data[x][3] = rs.getString("final_place");
					data[x][4] = rs.getString("starting_time");
					data[x][5] = rs.getString("starting_date");
				}
				query.close();
				query2.close();
			} catch (SQLException e) {
				logger.error("", e);
				e.printStackTrace();
			}
		}
		return data;
	}

	/**
	 * Informace o souperich
	 * 
	 * @param login
	 *            oponenta
	 * @return pole s informacemi o oponentovi
	 */
	public static String[] getPlayersInfo(String login) {
		ResultSet rs;
		String[] res = new String[6];
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			try {
				PreparedStatement query = con
						.prepareStatement("SELECT email,best_rating,Note,won,lost,drawn FROM Player WHERE login= ?");
				query.setString(1, login);

				rs = query.executeQuery();
				rs.next();
				res[0] = rs.getString("email");
				res[1] = rs.getString("best_rating");
				res[2] = rs.getString("Note");
				res[3] = rs.getString("won");
				res[4] = rs.getString("lost");
				res[5] = rs.getString("drawn");
				query.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
		return res;
	}

	/**
	 * Upravuje informace o oponetovi
	 * 
	 * @param login
	 *            jeho login
	 * @param email
	 *            jeho email
	 * @param bestRating
	 *            jeho nejlepsi rating
	 * @param note
	 *            poznamka
	 */
	public static void editPlayer(String login, String email, int bestRating,
			String note) {
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			try {
				PreparedStatement query = con
						.prepareStatement("UPDATE Player SET email = ?, best_rating = ?, note = ? WHERE login = ?");
				query.setString(1, email);
				query.setInt(2, bestRating);
				query.setString(3, note);
				query.setString(4, login);
				logger.debug("MYSQL:" + query);
				query.executeUpdate();
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
	}

	/**
	 * Vklada protihrace do databaze
	 * 
	 * @param name
	 *            login protihrace
	 */
	public static void insertPlayer(String name) {
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			try {
				PreparedStatement query = con
						.prepareStatement("insert into Player (login) VALUES (?)");
				query.setString(1, name);
				logger.debug("MYSQL:" + query);
				query.executeUpdate();
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
	}

	/**
	 * Vklada informace o hre do databaze
	 * 
	 * @param style
	 *            styl
	 * @param time
	 *            casovy limit hry
	 * @param colour
	 *            barva figur se kterymi hraji
	 * @param name
	 *            login protihrace
	 */
	public static void insertGame(String style, int time, String colour,
			String name) {
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {

			actualLogin = name;
			long dat = Calendar.getInstance().getTimeInMillis();
			java.sql.Date t = new java.sql.Date(dat);

			long actualTime = Calendar.getInstance().getTimeInMillis();
			Time actualTimeSQL = new Time(actualTime);
			try {
				PreparedStatement query = con
						.prepareStatement("insert into Games VALUES (NULL,?,?,?,?,?,?,?,NULL,?)");
				query.setString(1, style);
				query.setInt(2, time);
				query.setString(3, colour);
				query.setString(4, "Running");
				query.setDate(5, t);
				query.setTime(6, actualTimeSQL);
				query.setString(7, name);
				query.setInt(8, actualId_session);
				logger.debug("MYSQL:" + query);
				query.executeUpdate();

				ResultSet rs;
				PreparedStatement query2 = con
						.prepareStatement("SELECT id_game FROM Games WHERE game_date = ? AND time_game= ?");
				query2.setDate(1, t);
				query2.setTime(2, actualTimeSQL);
				logger.debug(query2);

				rs = query2.executeQuery();
				while (rs.next()) {
					logger.debug("ID_GAME:" + rs.getInt("id_game"));
					actualId_game = rs.getInt("id_game");
				}
				query.close();
				query2.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
	}

	/**
	 * Upravuje vysledek hry v zaznamu o hre
	 * 
	 * @param result
	 *            vysledek hry
	 */
	public static void updateGameResult(String result) {
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			try {
				PreparedStatement query = con
						.prepareStatement("UPDATE Games SET Result = ? WHERE id_game = ?");
				query.setString(1, result);
				query.setInt(2, actualId_game);
				logger.debug("MYSQL:" + query);
				query.executeUpdate();
				query.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
	}

	/**
	 * Upravuje informaci o sezeni(pocet vyher/proher)
	 * 
	 * @param result
	 *            vysledek partie
	 */
	public static void updateSession(String result) {
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			try {
				PreparedStatement query1 = con
						.prepareStatement("UPDATE Session SET game_lost = ? WHERE id_session = ?");

				query1.setInt(2, actualId_session);

				PreparedStatement query2 = con
						.prepareStatement("UPDATE Session SET game_won = ? WHERE id_session = ?");

				query2.setInt(2, actualId_session);

				PreparedStatement query3 = con
						.prepareStatement("UPDATE Session SET game_drawn = ? WHERE id_session = ?");

				query3.setInt(2, actualId_session);
				if (result.equals("Lost")) {
					lost++;
					query1.setInt(1, lost);
					logger.debug("MYSQL:" + query1);
					query1.executeUpdate();
				} else if (result.equals("Won")) {
					won++;
					query2.setInt(1, won);
					logger.debug("MYSQL:" + query2);
					query2.executeUpdate();
				} else if (result.equals("Drawn")) {
					drawn++;
					query3.setInt(1, drawn);
					logger.debug("MYSQL:" + query3);
					query3.executeUpdate();
				}
				query1.close();
				query2.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
	}

	/**
	 * Upravuje informace o protihraci(pocet vyher/proher/remiz)
	 * 
	 * @param result
	 *            vysledek partie
	 */
	public static void updatePlayer(String result) {
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			try {
				PreparedStatement query1 = con
						.prepareStatement("UPDATE Player SET lost = ? WHERE login = ?");
				query1.setInt(1, getPlayerLost(actualLogin) + 1);
				query1.setString(2, actualLogin);

				PreparedStatement query2 = con
						.prepareStatement("UPDATE Player SET won = ? WHERE login = ?");
				query2.setInt(1, getPlayerWon(actualLogin) + 1);
				query2.setString(2, actualLogin);

				PreparedStatement query3 = con
						.prepareStatement("UPDATE Player SET drawn = ? WHERE login = ?");
				query3.setInt(1, getPlayerDrawn(actualLogin) + 1);
				query3.setString(2, actualLogin);
				if (result.equals("Lost")) {
					logger.debug("MYSQL:" + query1);
					query1.executeUpdate();
					query2.setInt(1, getPlayerWon(myLogin) + 1);
					query2.setString(2, myLogin);
					query2.executeUpdate();
				} else if (result.equals("Won")) {
					logger.debug("MYSQL:" + query2);
					query2.executeUpdate();
					query1.setInt(1, getPlayerLost(myLogin) + 1);
					query1.setString(2, myLogin);
					query1.executeUpdate();
				} else if (result.equals("Drawn")) {
					logger.debug("MYSQL:" + query3);
					query3.executeUpdate();
					query3.setInt(1, getPlayerDrawn(myLogin) + 1);
					query3.setString(2, myLogin);
					query3.executeUpdate();
				}

			} catch (SQLException e) {
				logger.error("", e);
			}
		}
	}

	/**
	 * Metoda vklada informace o tahu do databaze
	 * 
	 * @param from
	 *            ze ktereho pole tazeno
	 * @param to
	 *            na ktere pole tazeno
	 * @param time
	 *            cas do prohry v partii
	 * @param name
	 *            login protihrace
	 */
	public static void insertMove(String from, String to, int time, String name) {
		if (Boolean.valueOf(prefs.get("checkDatabase", "false"))) {
			try {
				PreparedStatement query = con
						.prepareStatement("insert into Moves VALUES (NULL,?,?,?,?,?,?)");
				query.setInt(1, moveNumber);
				query.setString(2, from);
				query.setString(3, to);
				query.setInt(4, time);
				query.setInt(5, actualId_game);
				query.setString(6, name);

				logger.debug("MYSQL:" + query);
				query.executeUpdate();
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
	}

	/**
	 * Nastaveni aktualniho loginu
	 * 
	 * @param myLogin
	 *            login pod kterym jsem pripojen k serveru
	 */
	public static void setMyLogin(String myLogin) {
		MySQLConnection.myLogin = myLogin;
	}

	/**
	 * Vraci login oponenta
	 * 
	 * @return login hrace ze kterym prave probiha partie
	 */
	public static String getActualLogin() {
		return actualLogin;
	}
}
